﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using System.Linq;
using System.Text;
using System.IO;
using PPI.Mobile.General;

namespace PPI.Mobile.Logic
{
    public class LMobileDatabase
    {

        public static void ExecuteSingleQueryWhithParameters(string query, string mobileConnection, SqlParameter[] parameter)
        {
            SqlCeConnection _Conn = null;

            try
            {
                _Conn = MobileGeneral.GetConnection(mobileConnection);
                SqlCeCommand _SqlCeCommand = null;
                _SqlCeCommand = new SqlCeCommand(query, _Conn);
                foreach (SqlParameter sqlParameter in parameter)
                {
                    SqlCeParameter tmpSqlParameter = convertParameterToMobileParameter(sqlParameter);
                    _SqlCeCommand.Parameters.Add(tmpSqlParameter);
                }
                _SqlCeCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Conn != null)
                    _Conn.Close();
            }


        }

        public static object ExecuteScalar(string query, string mobileConnectionString)
        {
            SqlCeConnection _Conn = null;
            SqlCeDataAdapter _Da = null;
            DataSet _Ds = null;
            try
            {
                _Conn = MobileGeneral.GetConnection(mobileConnectionString);
                _Da = new SqlCeDataAdapter(query, _Conn);
                return _Da.SelectCommand.ExecuteScalar(); ;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Conn != null)
                    _Conn.Close();
            }
        }

        public static void ExecuteQuery(string query, string mobileConnectionString)
        {
            SqlCeConnection _Conn = null;

            try
            {
                _Conn = MobileGeneral.GetConnection(mobileConnectionString);
                SqlCeCommand _SqlCeCommand = null;
                string[] querys = query.Split("\r\n".ToCharArray());
                foreach (string s in querys)
                {
                    if (!string.IsNullOrEmpty(s))
                    {
                        _SqlCeCommand = new SqlCeCommand(s, _Conn);
                        _SqlCeCommand.CommandType = CommandType.Text;
                        _SqlCeCommand.Prepare();
                        _SqlCeCommand.ExecuteNonQuery();

                    }
                }
                
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally 
            {
                if(_Conn != null)
                    _Conn.Close();
            }

            



        }

        public static void ExecuteQuery(List<EMobileQuery> queries, string mobileConnectionString)
        {
            SqlCeConnection _Conn = null;
            EMobileQuery tmpQuery = null;
            int i = 0;
            try
            {
                _Conn = MobileGeneral.GetConnection(mobileConnectionString);
                SqlCeCommand _SqlCeCommand = null;
                foreach (EMobileQuery q in queries)
                {
                    tmpQuery = q;
                    _SqlCeCommand = new SqlCeCommand(q.Query, _Conn);
                    _SqlCeCommand.CommandType = CommandType.Text;
                    _SqlCeCommand.Prepare();
                    if(q.HasParameters)
                    {
                        for (Int32 j = 0; j<q.Parameters.Count;j++ )
                        {
                            _SqlCeCommand.Parameters.Clear();
                            for(Int32 c = 0; c<q.Parameters[j].Count;c++)
                            {
                                SqlCeParameter tmpSqlParameter = convertParameterToMobileParameter(q.Parameters[j][c]);
                                _SqlCeCommand.Parameters.Add(tmpSqlParameter);

                            }
                            _SqlCeCommand.ExecuteNonQuery();
                        }
                    }else
                    {
                        _SqlCeCommand.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            finally
            {
                if (_Conn != null)
                    _Conn.Close();
            }





        }

        public static DataSet ExecuteQueryDataset(string query, string mobileConnectionString)
        {
            return ExecuteQueryDataset(query, mobileConnectionString, null);
        }

        public static DataSet ExecuteQueryDataset(string query, string mobileConnectionString, SqlParameter[] parameter)
        {
            SqlCeConnection _Conn = null;
            SqlCeDataAdapter _Da = null;
            DataSet _Ds = null;
            try
            {
                _Conn = MobileGeneral.GetConnection(mobileConnectionString);
                _Da = new SqlCeDataAdapter(query, _Conn);
                if(parameter != null)
                {
                    foreach (SqlParameter sqlParameter in parameter)
                    {
                        SqlCeParameter tmpSqlParameter = convertParameterToMobileParameter(sqlParameter);
                        _Da.SelectCommand.Parameters.Add(tmpSqlParameter);
                    }
                }
                _Ds = new DataSet();
                _Da.Fill(_Ds);
                return _Ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Conn != null)
                    _Conn.Close();
            }
        }

        public static SqlCeDataReader ExecuteReader(string query, string mobileConnectionString)
        {
            SqlCeConnection _Conn = null;
            SqlCeDataAdapter _Da = null;
            DataSet _Ds = null;
            try
            {
                _Conn = MobileGeneral.GetConnection(mobileConnectionString);
                SqlCeCommand command = new SqlCeCommand(query, _Conn);

                return command.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Conn != null)
                    _Conn.Close();
            }
        }

        private static SqlCeParameter convertParameterToMobileParameter(SqlParameter sqlParameter)
        {
            SqlCeParameter tmpSqlParameter = new SqlCeParameter(sqlParameter.ParameterName, sqlParameter.SqlDbType, sqlParameter.Size, sqlParameter.Direction, sqlParameter.IsNullable, sqlParameter.Precision, sqlParameter.Scale, sqlParameter.SourceColumn, sqlParameter.SourceVersion, sqlParameter.Value);
            return tmpSqlParameter;
        }
    }
}
